DROP TRIGGER UPDATE_STUDENT_ID_TRI;
DROP SEQUENCE STUDENT_ID_SEQ;
DROP TABLE COURSES;
DROP TABLE STUDENTS;

--创建表
CREATE TABLE STUDENTS(
  STUDENT_ID INT PRIMARY KEY,
  STUDENT_NAME VARCHAR2(20) NOT NULL,
  SEX INT NOT NULL,
  CONSTRAINT STUDENTS_SEX_CHK CHECK (SEX IN(1,0))
);

CREATE TABLE COURSES(
  STUDENT_ID INT REFERENCES STUDENTS (STUDENT_ID),
  CHINESE INT CHECK(CHINESE>=0 AND CHINESE<=100) NOT NULL,
  MATH INT CHECK(MATH>=0 AND MATH<=100) NOT NULL,
  ENGLISH INT CHECK(ENGLISH>=0 AND ENGLISH<=100) NOT NULL,
  POLITICS INT CHECK(POLITICS>=0 AND POLITICS<=100) NOT NULL
);

--创建序列
CREATE SEQUENCE STUDENT_ID_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

--创建触发器
CREATE TRIGGER UPDATE_STUDENT_ID_TRI BEFORE
INSERT ON STUDENTS FOR EACH ROW WHEN(NEW.STUDENT_ID IS NULL)
BEGIN
SELECT STUDENT_ID_SEQ.NEXTVAL INTO:NEW.STUDENT_ID FROM DUAL;
END;
